//Copyright 2008 by Wes Corp for Trafficman  All rights reserved.abstract 
package org.trafficman.trafficman_main;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import java.io.FileNotFoundException;

public class TrafficmanLocationDbHelper {

    public static final String KEY_ROWID_ADDRESS = "_id";
    public static final String KEY_ROWID_LINE = "_id";
    public static final String KEY_ROWID_EXTRA = "_id";
    public static final String KEY_TM_LOCALITY = "locality";
    public static final String KEY_TM_LOCATION_ID = "location_Id";
    public static final String KEY_TM_LINENUMBER = "lineNumber";
    public static final String KEY_TM_LINETEXT = "lineText";
    public static final String KEY_TM_KEY = "key";
    public static final String KEY_TM_DATA = "data";
    public static final String KEY_TM_REGION = "region";
    public static final String KEY_TM_POSTALCODE = "postalCode";
    public static final String KEY_TM_COUNTRYCODE = "countryCode";
    public static final String KEY_TM_COUNTRYNAME = "countryName";
    public static final String KEY_TM_LATITUDE = "latitude";
    public static final String KEY_TM_LONGITUDE = "longitude";
    public static final String KEY_TM_PHONE = "phone";
    public static final String KEY_TM_URL = "url";
    public static final String KEY_TM_DATETIME_CREATED = "datetime_record_created";
    public static final String KEY_TM_DATETIME_UPDATED = "datetime_record_updated";
    private SQLiteDatabase mDb;
    private final Context mCtx;
    private String str_Location_Table;
    private long lng_LocationId;

    public TrafficmanLocationDbHelper(Context ctx) {
        this.mCtx = ctx;
    }

    public TrafficmanLocationDbHelper open() throws SQLException {

        if (TrafficmanConstants.ACTIVE_DEVICE.equals("sdcard")) {
            mDb = SQLiteDatabase.open(TrafficmanConstants.DATABASE_LOCATIONS_SDCARD, null);
            if (mDb == null) {
                mDb = SQLiteDatabase.create(TrafficmanConstants.DATABASE_LOCATIONS_SDCARD,
                        TrafficmanConstants.DATABASE_VERSION, null);
                mDb.execSQL("create table if not exists locations" + TrafficmanConstants.DV_VER + " (_id integer primary key autoincrement, locality text not null, region text not null, postalCode text not null, countryCode text not null, countryName text not null, latitude text not null, longitude text not null, phone text not null, url text not null, datetime_record_created text not null, datetime_record_updated text not null);");
                mDb.execSQL("create table if not exists lines" + TrafficmanConstants.DV_VER + " (_id integer primary key autoincrement, location_Id integer, lineNumber integer, lineText text not null);");
                mDb.execSQL("create table if not exists extra" + TrafficmanConstants.DV_VER + " (_id integer primary key autoincrement, location_Id integer, key text not null, data text not null);");
            }
            str_Location_Table = TrafficmanConstants.DATABASE_TABLE_LOCATIONS;
        } else {
            try {
                mDb = mCtx.openDatabase(TrafficmanConstants.DATABASE_LOCATIONS, null);
                str_Location_Table = TrafficmanConstants.DATABASE_TABLE_JV_FILES;
            } catch (FileNotFoundException e) {
                try {
                    mDb = mCtx.createDatabase(TrafficmanConstants.DATABASE_LOCATIONS,
                            TrafficmanConstants.DATABASE_VERSION, 0, null);
                    mDb.execSQL("create table if not exists locations" + TrafficmanConstants.DV_VER + " (_id integer primary key autoincrement, locality text not null, region text not null, postalCode text not null, countryCode text not null, countryName text not null, latitude text not null, longitude text not null, phone text not null, url text not null, datetime_record_created text not null, datetime_record_updated text not null);");
                    mDb.execSQL("create table if not exists lines" + TrafficmanConstants.DV_VER + " (_id integer primary key autoincrement, location_Id integer, lineNumber integer, lineText text not null);");
                    mDb.execSQL("create table if not exists extra" + TrafficmanConstants.DV_VER + " (_id integer primary key autoincrement, location_Id integer, key text not null, data text not null);");
                    str_Location_Table = TrafficmanConstants.DATABASE_TABLE_LOCATIONS;
                } catch (FileNotFoundException e1) {
                    throw new SQLException("Could not create database");
                }
            }
        }
        return this;
    }

    public void close() {
        mDb.close();
    }

    
    
    public long createLocation(String locality, String region, String postalCode, String countryCode, String countryName, String latitude, String longitude,
            String phone, String url, String datetime_record_created, String datetime_record_updated) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TM_LOCALITY, locality);
        initialValues.put(KEY_TM_REGION, region);
        initialValues.put(KEY_TM_POSTALCODE, postalCode);
        initialValues.put(KEY_TM_COUNTRYCODE, countryCode);
        initialValues.put(KEY_TM_COUNTRYNAME, countryName);
        initialValues.put(KEY_TM_LATITUDE, latitude);
        initialValues.put(KEY_TM_LONGITUDE, longitude);
        initialValues.put(KEY_TM_PHONE, phone);
        initialValues.put(KEY_TM_URL, url);
        initialValues.put(KEY_TM_DATETIME_CREATED, datetime_record_created);
        initialValues.put(KEY_TM_DATETIME_UPDATED, datetime_record_updated);
        return mDb.insert(str_Location_Table, null,
                initialValues);
    }

    public long createLine(Long location_Id, Integer lineNumber, String lineText) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TM_LOCATION_ID, location_Id);
        initialValues.put(KEY_TM_LINENUMBER, lineNumber);
        initialValues.put(KEY_TM_LINETEXT, lineText);
        return mDb.insert(TrafficmanConstants.DATABASE_TABLE_LOCATIONS_LINES, null,
                initialValues);
    }

    public long createExtra(Long location_Id, String key, String data) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TM_LOCATION_ID, location_Id);
        initialValues.put(KEY_TM_KEY, key);
        initialValues.put(KEY_TM_DATA, data);
        return mDb.insert(TrafficmanConstants.DATABASE_TABLE_LOCATIONS_EXTRA, null,
                initialValues);
    }

    public boolean deleteNote(long rowId) {
        return mDb.delete(str_Location_Table, KEY_ROWID_ADDRESS + "=" + rowId, null) > 0;
    }

    public boolean deleteLine(long rowId) {
        return mDb.delete(TrafficmanConstants.DATABASE_TABLE_LOCATIONS_LINES, KEY_ROWID_LINE + "=" + rowId, null) > 0;
    }

    public boolean deleteExtra(long rowId) {
        return mDb.delete(TrafficmanConstants.DATABASE_TABLE_LOCATIONS_EXTRA, KEY_ROWID_EXTRA + "=" + rowId, null) > 0;
    }

    public Cursor fetchLocations() {
        return mDb.query(str_Location_Table, new String[]{
                    KEY_ROWID_ADDRESS, KEY_TM_LOCALITY, KEY_TM_REGION, KEY_TM_POSTALCODE,
                    KEY_TM_COUNTRYCODE, KEY_TM_COUNTRYNAME, KEY_TM_LATITUDE, KEY_TM_LONGITUDE, KEY_TM_PHONE, KEY_TM_URL,
                    KEY_TM_DATETIME_CREATED, KEY_TM_DATETIME_UPDATED
                }, null, null,
                null, null, null);
    }

    public Cursor fetchNote(long rowId) throws SQLException {
        Cursor result = mDb.query(true, str_Location_Table,
                new String[]{KEY_ROWID_ADDRESS, KEY_TM_LOCALITY, KEY_TM_REGION, KEY_TM_POSTALCODE,
                    KEY_TM_COUNTRYCODE, KEY_TM_COUNTRYNAME, KEY_TM_LATITUDE, KEY_TM_LONGITUDE, KEY_TM_PHONE, KEY_TM_URL,
                    KEY_TM_DATETIME_CREATED, KEY_TM_DATETIME_UPDATED
                }, KEY_ROWID_ADDRESS + "=" + rowId,
                null, null, null, null);
        if ((result.count() == 0) || !result.first()) {
            // throw new SQLException("No note matching ID: " + rowId);
        }
        return result;
    }

    public Cursor fetchLine(long rowId) throws SQLException {
        Cursor result = mDb.query(true, TrafficmanConstants.DATABASE_TABLE_LOCATIONS_LINES,
                new String[]{KEY_ROWID_LINE, KEY_TM_LOCATION_ID, KEY_TM_LINENUMBER, KEY_TM_LINETEXT}, KEY_ROWID_LINE + "=" + rowId,
                null, null, null, null);
        if ((result.count() == 0) || !result.first()) {
            // throw new SQLException("No note matching ID: " + rowId);
        }
        return result;
    }

    public Cursor fetchExtra(long rowId) throws SQLException {
        Cursor result = mDb.query(true, str_Location_Table,
                new String[]{KEY_ROWID_EXTRA, KEY_TM_LOCATION_ID, KEY_TM_KEY, KEY_TM_DATA}, KEY_ROWID_ADDRESS + "=" + rowId,
                null, null, null, null);
        if ((result.count() == 0) || !result.first()) {
            // throw new SQLException("No note matching ID: " + rowId);
        }
        return result;
    }
    public boolean updateNote(long rowId, String locality, String region, String postalCode, String countryCode, String countryName, String latitude, String longitude,
            String phone, String url, String datetime_record_created, String datetime_record_updated) {
        ContentValues args = new ContentValues();
        args.put(KEY_TM_LOCALITY, locality);
        args.put(KEY_TM_REGION, region);
        args.put(KEY_TM_POSTALCODE, postalCode);
        args.put(KEY_TM_COUNTRYCODE, countryCode);
        args.put(KEY_TM_COUNTRYNAME, countryName);
        args.put(KEY_TM_LATITUDE, latitude);
        args.put(KEY_TM_LONGITUDE, longitude);
        args.put(KEY_TM_PHONE, phone);
        args.put(KEY_TM_URL, url);
        args.put(KEY_TM_DATETIME_CREATED, datetime_record_created);
        args.put(KEY_TM_DATETIME_UPDATED, datetime_record_updated);
        return mDb.update(str_Location_Table, args, KEY_ROWID_ADDRESS + "=" + rowId, null) > 0;
    }

    public boolean updateLine(long rowId, Integer location_Id, Integer lineNumber, String lineText) {
        ContentValues args = new ContentValues();
        args.put(KEY_TM_LOCATION_ID, location_Id);
        args.put(KEY_TM_LINENUMBER, lineNumber);
        args.put(KEY_TM_LINETEXT, lineText);
        return mDb.update(TrafficmanConstants.DATABASE_TABLE_LOCATIONS_LINES, args, KEY_ROWID_LINE + "=" + rowId, null) > 0;
    }

    public boolean updateExtra(long rowId, Integer location_Id, String key, String data) {
        ContentValues args = new ContentValues();
        args.put(KEY_TM_LOCATION_ID, location_Id);
        args.put(KEY_TM_KEY, key);
        args.put(KEY_TM_DATA, data);
        return mDb.update(TrafficmanConstants.DATABASE_TABLE_LOCATIONS_EXTRA, args, KEY_ROWID_EXTRA + "=" + rowId, null) > 0;
    }
}
